<?php
/*
 * (C) Copyright 2013 Torsten Gorath (http://www.blauesdach.de/).
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 * Contributors:
 *     Torsten Gorath
 */
require_once $basedir."/inc/table.php";
require_once $basedir."/inc/kinder.php";
require_once $basedir."/inc/bankverbindung.php";

class Vertraege extends table {
    
    public static $TABLENAME = "vertraege";
    public static $CLASSNAME = "Vertraege";
    
    private $id = "";
    private $kind_id  = "";
    private $bank_id = "";
    private $vertrag_ab = "";
	private $betreuung_von = "";
	private $betreuung_bis = "";
    private $darlehen = 0;
    private $darlehen_eingezogen_am = "";
    private $darlehen_zurueck_am = "";
    private $aufnahmegebuehr = 0;
    private $aufnahmegebuehr_eingezogen_am = "";
    private $baugeld = 0;
    private $baugeld_eingezogen_am = "";
    private $monatsbeitrag = 0;
    private $sozialamt = 0;
    private $zuschuss_stadt = 0;
    private $spende = 0;
    private $hygienebeitrag = 0;
    private $ausgeschieden_am = "";
    
    public function __construct($id="") {
		if($id!='') {
            $temp = Database::getDefault()->openId("vertraege",$id);
            foreach($temp as $key => $value) {
                $this->$key = $value;
            }
		}
    }
    
    public function delete() {
        Database::getDefault()->delete("vertraege",$this->id);
    }
    
    public function save() {
    	$values = array();
		foreach ($this as $key => $value) {
			$values[$key] = $value;
		}
        if($this->id=="") {
            $this->id = Database::getDefault()->insert("vertraege", $values);
        } else {
            Database::getDefault()->update("vertraege", $values, $this->id);
        }
    }
    
    /**
     * get all contracts that have the defined properties
     * 
     * @param $aktive default true, only currently running contracts
     * @param $end_date default null, this mean now or yyyy-MM-dd to calculate date
     * @return array of Vertraege instances
     */
    static public function getAll($aktive=true, $end_date=null, $kind_id=null) {
        global $VEREIN;
        if($end_date != null) {
            $aktive = true;
        }
        $query = "SELECT DISTINCT Vertraege.* FROM " . $VEREIN['prefix'] . "vertraege AS Vertraege, " . $VEREIN['prefix'] . "kinder AS Kinder WHERE Kinder.id=Vertraege.kind_id";
		if($aktive) {
		    if($end_date != null) {
//                $query .= " AND ((ausgeschieden_am>".$end_date;
                $query .= " AND (vertrag_ab>='".$end_date."' AND (ausgeschieden_am>'".$end_date."'";
		    } else {
		        $query .= " AND (ausgeschieden_am>=NOW()";
		    }
			$query .= " OR ausgeschieden_am='0000-00-00')";
            if($end_date != null) {
                $query .= ")";
            }
		}
        if($kind_id != null) {
            $query .= " AND Kinder.id='".$kind_id."'";
        }
        $query .= " ORDER BY Kinder.nachname, Kinder.vorname";
        return Database::getDefault()->execute($query,"Vertraege");
    }

    public function getId() {
        return $this->id;
    }
    
    public function setKindId($value) {
        $this->kind_id = $value;
    }
    
    public function getKindId() {
        return $this->kind_id;
    }

	public function getKind() {
		return new Kinder($this->kind_id);
	}
	 
    public function setBankId($value) {
        $this->bank_id = $value;
    }
    
    public function getBankId() {
        return $this->bank_id;
    }
	
	public function getBank() {
		return new Bankverbindung($this->bank_id);
	}
    
    public function setVertragAb($value) {
        $this->vertrag_ab = $value;
    }
    
    public function getVertragAb() {
    	if($this->vertrag_ab=="0000-00-00") return "";
        return $this->vertrag_ab;
    }
    
    public function setBetreuungVon($value) {
    	$this->betreuung_von = $value;
    }
    
    public function getBetreuungVon() {
    	return $this->betreuung_von;
    }
	
	public function setBetreuungBis($value) {
		$this->betreuung_bis = $value;
	}
	
	public function getBetreuungBis() {
		return $this->betreuung_bis;
	}
	
    public function setDarlehen($value) {
        $this->darlehen = $value;
    }
    
    public function getDarlehen() {
    	if($this->darlehen==0) {
    		//return $this->getKind()->getDarlehen();
    	}
        return $this->darlehen;
    }
    
    public function setDarlehenEingezogenAm($value) {
        $this->darlehen_eingezogen_am = $value;
    }
    
    public function getDarlehenEingezogenAm() {
    	if($this->darlehen_eingezogen_am=="0000-00-00") return "";
        return $this->darlehen_eingezogen_am;
    }
    
    public function setDarlehenZurueckAm($value) {
        $this->darlehen_zurueck_am = $value;
    }
    
    public function getDarlehenZurueckAm() {
    	if($this->darlehen_zurueck_am=="0000-00-00") return "";
        return $this->darlehen_zurueck_am;
    }
    
    public function setAufnahmegebuehr($value) {
        $this->aufnahmegebuehr = $value;
    }
    
    public function getAufnahmebgebuehr() {
        return $this->aufnahmegebuehr;
    }
    
    public function setAufnahmegebuehrEingezogenAm($value) {
        $this->aufnahmegebuehr_eingezogen_am = $value;
    }
    
    public function getAufnahmegebuehrEingezogenAm() {
    	if($this->aufnahmegebuehr_eingezogen_am=="0000-00-00") return "";
        return $this->aufnahmegebuehr_eingezogen_am;
    }
    
    public function setBaugeld($value) {
        $this->baugeld = $value;
    }
    
    public function getBaugeld() {
        return $this->baugeld;
    }
    
    public function setBaugeldEingezogenAm($value) {
        $this->baugeld_eingezogen_am = $value;
    }
    
    public function getBaugeldEingezogenAm() {
    	if($this->baugeld_eingezogen_am=="0000-00-00") return "";
        return $this->baugeld_eingezogen_am;
    }
    
    public function setMonatsbeitrag($value) {
        $this->monatsbeitrag = $value;
    }
    
    public function getMonatsbeitrag() {
        return $this->monatsbeitrag;
    }
    
    public function setSozialamt($value) {
    	if($value=="") $value = 0;
        $this->sozialamt = $value;
    }
    
    public function getSozialamt() {
        return $this->sozialamt;
    }
    
    public function setZuschussStadt($value) {
        if($value == "") {
            $value = 0;
        }
        $this->zuschuss_stadt = $value;
    }
    
    public function getZuschussStadt() {
        return $this->zuschuss_stadt;
    }
    
    public function setSpende($value) {
        $this->spende = $value;
    }
    
    public function getSpende() {
        return $this->spende;
    }
    
    public function setHygienebeitrag($value) {
        $this->hygienebeitrag = $value;
    }
    
    public function getHygienebeitrag() {
        return $this->hygienebeitrag;
    }
    
    public function setAusgeschiedenAm($value) {
        $this->ausgeschieden_am = $value;
    }
    
    public function getAusgeschiedenAm() {
    	if($this->ausgeschieden_am=="0000-00-00") return "";
        return $this->ausgeschieden_am;
    }
    
    /**
     * create table for banking contacts or update if something to do
     */
    public static function initDatabase() {
        global $VEREIN;
        echo "<b>update / create " . Vertraege::$TABLENAME . "</b><br />";
        $query = "CREATE TABLE IF NOT EXISTS " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            die(Database::getDefault()->getConnection()->error);
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN kind_id INT UNSIGNED DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN bank_id INT UNSIGNED DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN vertrag_ab DATE DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN betreuung_von TIME DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN betreuung_bis TIME DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN darlehen DECIMAL NOT NULL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN darlehen_eingezogen_am DATE DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN darlehen_zurueck_am DATE DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN aufnahmegebuehr DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN aufnahmegebuehr_eingezogen_am DATE DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN baugeld DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN baugeld_eingezogen_am DATE DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN monatsbeitrag DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN sozialamt DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN zuschuss_stadt DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN spende DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN hygienebeitrag DECIMAL DEFAULT 0";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        $query = "ALTER IGNORE TABLE " . $VEREIN['prefix'] . Vertraege::$TABLENAME . " ADD COLUMN ausgeschieden_am DATE DEFAULT NULL";
        echo $query . "<br />";
        try {
            $result = Database::getDefault()->executePrepare($query);
        } catch(Exception $e) {
            if(!$result && Database::getDefault()->getConnection()->errno != 1060) {
                die(Database::getDefault()->getConnection()->error);
            }
        }
        echo "<br />";
    }
}
?>